/*
EXEC SP_CREATE_UPDATE_SCRAPPED_LOT 13358, 435, '', 'MANULLAY INSERT', 0, 'Admin'
*/
ALTER PROC SP_CREATE_UPDATE_SCRAPPED_LOT
(
	@LOT_ID				INT,
	@SCRAPPED_QTY_IN	INT,
	@SCRAPPED_WAFERIDS	VARCHAR(300),
	@COMMENTS 			VARCHAR(500),
	@NONVALUE_INVENTORY INT,
	@USER_NAME			VARCHAR(50)
)
AS
BEGIN
	
	DECLARE @PRV_SCRAPED_WAFERIDS 		VARCHAR(300)
	DECLARE @PRV_SCRAPED_WAFERIDS_AS_COMMA	 VARCHAR(300)
    DECLARE @SCRAPPED_WAFERIDS_AS_COMMA	 VARCHAR(300)
	DECLARE @PRV_SCRAPED_WAFER_IDS_TABLE 	TABLE ( WAFER_ID INT )
	DECLARE	@SCRAPED_WAFER_IDS_TABLE    TABLE ( WAFER_ID INT )
	DECLARE @WAFER_IDS_TABLE 		TABLE ( WAFER_ID INT )
	DECLARE @CONCAT_WAFERS_INTO_STRING 	VARCHAR(300)
	
	DECLARE @QTY_BAL INT, @SCRAP_WAFER_QTY INT
	
	DECLARE @PS_TYPE_NAME VARCHAR(50)	

	SELECT @PS_TYPE_NAME = PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = ( SELECT PROCESSSTEP_ID FROM LOT_TABLE WHERE LOT_ID = @LOT_ID )
	
	IF @SCRAPPED_QTY_IN > 0  
	BEGIN
			IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' OR @PS_TYPE_NAME = 'BUMPING'
			BEGIN
				SET @SCRAP_WAFER_QTY = @SCRAPPED_QTY_IN
				SET @SCRAPPED_QTY_IN = 0
			END	

			IF( ( SELECT COUNT(*) FROM SCRAPPED_LOTS WHERE LOT_ID = @LOT_ID AND SL_SCRAPPED_DATE = CONVERT( VARCHAR, GETDATE(), 101 ) AND IS_NONVALUE_INVENTORY = @NONVALUE_INVENTORY ) = 0 ) 
			BEGIN				
					INSERT INTO SCRAPPED_LOTS ( LOT_ID, SL_SCRAPPED_DIE_QTY,SL_SCRAPPED_WAFER_QTY, SL_SCRAPPED_WAFER_IDS, SL_SCRAPPED_DATE, SL_GENERATED_BY, SL_GENERATED_ON, [TIMESTAMP], SL_COMMENTS, IS_NONVALUE_INVENTORY )
						VALUES ( @LOT_ID, @SCRAPPED_QTY_IN, @SCRAP_WAFER_QTY, @SCRAPPED_WAFERIDS, CONVERT( VARCHAR, GETDATE(), 101 ), @USER_NAME, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, @COMMENTS , @NONVALUE_INVENTORY)
			END
			ELSE
			BEGIN
				INSERT INTO SCRAPPED_LOTS_HISTORY SELECT * FROM SCRAPPED_LOTS WHERE LOT_ID = @LOT_ID AND SL_SCRAPPED_DATE = CONVERT( VARCHAR, GETDATE(), 101 ) AND IS_NONVALUE_INVENTORY = @NONVALUE_INVENTORY
				/* Partial Scrapping for One wafer is useful*/
				SELECT @PRV_SCRAPED_WAFERIDS = SL_SCRAPPED_WAFER_IDS FROM SCRAPPED_LOTS WHERE LOT_ID = @LOT_ID AND SL_SCRAPPED_DATE = CONVERT( VARCHAR, GETDATE(), 101 ) AND IS_NONVALUE_INVENTORY = @NONVALUE_INVENTORY

				SET @PRV_SCRAPED_WAFERIDS_AS_COMMA = REPLACE(@PRV_SCRAPED_WAFERIDS, ';',',')
				SET @SCRAPPED_WAFERIDS_AS_COMMA = REPLACE(@SCRAPPED_WAFERIDS, ';',',')

				INSERT INTO @PRV_SCRAPED_WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @PRV_SCRAPED_WAFERIDS_AS_COMMA   )		
				INSERT INTO @SCRAPED_WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @SCRAPPED_WAFERIDS_AS_COMMA  )		
				
				INSERT INTO @WAFER_IDS_TABLE SELECT WAFER_ID FROM @PRV_SCRAPED_WAFER_IDS_TABLE UNION SELECT WAFER_ID FROM @SCRAPED_WAFER_IDS_TABLE

				SET @CONCAT_WAFERS_INTO_STRING = NULL
		
				SELECT @CONCAT_WAFERS_INTO_STRING = COALESCE( @CONCAT_WAFERS_INTO_STRING + ';' + CONVERT( VARCHAR(2), WAFER_ID ), CONVERT( VARCHAR(2) , WAFER_ID ) )
						FROM @WAFER_IDS_TABLE ORDER BY WAFER_ID 
				/* END Partial Scrapping */

				UPDATE SCRAPPED_LOTS SET SL_SCRAPPED_DIE_QTY 	= ISNULL( SL_SCRAPPED_DIE_QTY, 0 )+ @SCRAPPED_QTY_IN, 
							 SL_SCRAPPED_WAFER_QTY 	= ISNULL( SL_SCRAPPED_WAFER_QTY, 0 )+ @SCRAP_WAFER_QTY, 
							 SL_SCRAPPED_DATE	= CONVERT( VARCHAR, GETDATE(), 101 ),
							 SL_SCRAPPED_WAFER_IDS 	= @CONCAT_WAFERS_INTO_STRING, 
							 SL_UPDATED_BY		= @USER_NAME,
							 [TIMESTAMP] 		= CURRENT_TIMESTAMP,
							 SL_COMMENTS		= @COMMENTS
				WHERE LOT_ID = @LOT_ID AND SL_SCRAPPED_DATE = CONVERT( VARCHAR, GETDATE(), 101 ) AND IS_NONVALUE_INVENTORY = @NONVALUE_INVENTORY
			END		
			
			IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' OR @PS_TYPE_NAME = 'BUMPING'
			BEGIN
				UPDATE LOT_TABLE SET WAFER_QTY_IN = Wafer_QTY_IN - @SCRAP_WAFER_QTY WHERE LOT_ID = @LOT_ID
				/* CLOSING LOT */
				IF( ( SELECT WAFER_QTY_IN FROM LOT_TABLE WHERE LOT_ID = @LOT_ID ) <= 0 )
				BEGIN
					UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @LOT_ID
				END
			END	
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET QTY_IN = QTY_IN - @SCRAPPED_QTY_IN WHERE LOT_ID = @LOT_ID
				/* CLOSING LOT */
				IF( ( SELECT WAFER_QTY_IN FROM LOT_TABLE WHERE LOT_ID = @LOT_ID ) <= 0 )
				BEGIN
					UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @LOT_ID
				END
			END				
			IF @PS_TYPE_NAME = 'Finished Goods'
			BEGIN
					SELECT @QTY_BAL = ISNULL(IL_QTY_BALANCE,0) FROM INVENTORY_LOT WHERE LOT_ID = @LOT_ID
					IF @QTY_BAL > 0
					BEGIN
							UPDATE INVENTORY_LOT SET IL_QTY_BALANCE = IL_QTY_BALANCE - @SCRAPPED_QTY_IN WHERE LOT_ID = @LOT_ID
					END
			END
			
	END
END